package com.ztc.ZtcClasses.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.aqgj.User.bean.User;
import com.aqgj.util.BaseDaoImpl;
import com.ztc.ZtcClasses.bean.ZtcClasses;
import com.ztc.ZtcClassesForSale.bean.ZtcClassesForSale;
import com.ztc.ZtcClassesForSale.dao.ZtcClassesForSaleDao;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 *
 * </p>
 *
 * @ClassName ZtcClassesDao
 * @JDK 1.5
 * @author
 * @date
 */
@Repository("ZtcClassesDao")
public class ZtcClassesDao extends BaseDaoImpl<ZtcClasses> {
	private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
	private int pageSize = 10;
	@Autowired
	private ZtcClassesForSaleDao ztcClassesForSaleDao;
	public String save(ZtcClasses ztcClasses) {
		this.getHibernateTemplate().save(ztcClasses);
		return ztcClasses.getId();
	}

	public String batchInsert(List<Date> departureAllDates, String name, User user, String carType, String carId,
			String driverId, String ztcClassesId, String remark, String isRefund, String fsTimerDeparture,String seatNumber,
			String childSeatNumber, String transportId, JSONArray siteArray,
			JSONArray priceArray) {
		Session session = getSessionFactory().openSession();
		session.doWork(new Work(){
			@Override
			public void execute(Connection connection) throws SQLException {
				connection.setAutoCommit(false);
				PreparedStatement stmt1 = connection.prepareStatement("insert into ztc_classes_site_for_sale(id,name,sequence,status,lat,area_city,area_code,departure_time,fk_ztc_classes_for_sale_id,manager_phone,type) values(?,?,?,?,?,?,?,?,?,?,?)");
//				PreparedStatement stmt2 = connection.prepareStatement("insert into ztc_classes_price_for_sale(id,distance,price,status,remain_seat_number,remain_child_seat_number,seat_info,fk_start_classes_site_for_sale_id,fk_end_classes_site_for_sale_id,fk_classes_for_sale_id,type) values(?,?,?,?,?,?,?,?,?,?,?)");
//				只添加时间属性
 				PreparedStatement stmt2 = connection.prepareStatement("insert into ztc_classes_price_for_sale(id,distance,price,status,remain_seat_number,remain_child_seat_number,seat_info,fk_start_classes_site_for_sale_id,fk_end_classes_site_for_sale_id,start_date,fk_classes_for_sale_id,type) values(?,?,?,?,?,?,?,?,?,?,?,?)");
				//全部属性添加后
//				PreparedStatement stmt2 = connection.prepareStatement("insert into ztc_classes_price_for_sale(id,distance,price,status,remain_seat_number,remain_child_seat_number,seat_info,fk_start_classes_site_for_sale_id,fk_start_city,fk_start_name,fk_end_classes_site_for_sale_id,fk_end_city,fk_end_name,start_date,start_time,fk_classes_for_sale_id,type) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

				String uuid;
				Map<Integer, String> siteMap;
				Map<Integer, String> siteMapName;
				Map<Integer, String> siteMapCity;
				Map<Integer, String> siteMapTime;
				int count1 = 0;
				int count2 = 0;
				for (int i = 0; i < departureAllDates.size(); i++) {
					Date getDepartureDate = departureAllDates.get(i);
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					String  stringGetDepartureDate =sdf.format(getDepartureDate) ;
					ZtcClassesForSale classesForSale = new ZtcClassesForSale();
					classesForSale.setName(name);
					classesForSale.setRemark(remark);
					classesForSale.setStatus(0);
					classesForSale.setTicketStatus(0);
					classesForSale.setCarType(Integer.parseInt(carType));
					classesForSale.setIsRefund("true".equals(isRefund) ? 1 : 0);
					classesForSale.setDepartureDate(getDepartureDate);
					classesForSale.setForbidSaleTimeRegionBeforeDeparture(fsTimerDeparture);
					classesForSale.setAddTime(new Date());
					classesForSale.setFkAddUserId(user == null ? "" : user.getId());
					classesForSale.setFkZtcCarId(carId);
					classesForSale.setType(0);
					classesForSale.setFkZtcDriverId(driverId);
					classesForSale.setFkZtcClassesId(ztcClassesId);
					classesForSale.setChildSeatNumber(Integer.parseInt(childSeatNumber));
					classesForSale.setSeatNumber(Integer.parseInt(seatNumber));
					classesForSale.setFkTransportId(transportId);
					String id = ztcClassesForSaleDao.save(classesForSale);
					if (id != null) {
						// 获取并创建站点对象
						siteMap = new HashMap<>();
						siteMapName = new HashMap<>();
						siteMapCity = new HashMap<>();
						siteMapTime = new HashMap<>();
						for (int j = 0; j < siteArray.size(); j++) {
							JSONObject object = siteArray.getJSONObject(j);
							if (object == null || object.isEmpty()) {
								continue;
							}
							uuid = UUID.randomUUID().toString().replace("-", "");
							stmt1.setString(1,uuid);
							stmt1.setString(2,object.getString("name"));
							stmt1.setInt(3, object.getInteger("row"));
							stmt1.setInt(4,0);
							stmt1.setString(5,object.getString("lat"));
							stmt1.setString(6,object.getString("areaCity"));
							stmt1.setString(7,object.getString("areaCode"));
							Calendar calendar = Calendar.getInstance();
							calendar.setTime(classesForSale.getDepartureDate());
							calendar.add(Calendar.DATE, Integer.parseInt(object.getString("daysAfterDepartureDate")));
							stmt1.setString(8,DATE_FORMAT.format(calendar.getTime())+" "+object.getString("departureTime")+":00");
							stmt1.setString(9,classesForSale.getId());
							stmt1.setString(10,object.getString("managerPhone"));
							stmt1.setInt(11,0);
							siteMap.put(object.getInteger("row"), uuid);
							siteMapName.put(object.getInteger("row"), object.getString("name"));
							siteMapCity.put(object.getInteger("row"), object.getString("areaCity"));
							siteMapTime.put(object.getInteger("row"), DATE_FORMAT.format(calendar.getTime())+" "+object.getString("departureTime")+":00");

							stmt1.addBatch();
							if(count1 % 1000 == 0){
								stmt1.executeBatch();
								connection.commit();
							}
							count1 ++;
						}
						for (int k = 0; k < priceArray.size(); k++) {
							JSONObject priceObject = priceArray.getJSONObject(k);
							if (priceObject == null || priceObject.isEmpty()) {
								continue;
							}
							uuid = UUID.randomUUID().toString().replace("-", "");
							stmt2.setString(1,uuid);
							stmt2.setString(2,priceObject.getString("distance"));
							stmt2.setString(3,priceObject.getString("price"));
							stmt2.setInt(4,0);
							stmt2.setInt(5,Integer.parseInt(seatNumber));
							stmt2.setInt(6,Integer.parseInt(childSeatNumber));
							stmt2.setString(7, priceObject.getString("seatInfo"));
							stmt2.setString(8,siteMap.get(priceObject.getInteger("col")));
//							stmt2.setString(9,siteMapCity.get(priceObject.getInteger("col")));
//							stmt2.setString(10,siteMapName.get(priceObject.getInteger("col")));
							stmt2.setString(9,siteMap.get(priceObject.getInteger("row")));
//							stmt2.setString(12,siteMapCity.get(priceObject.getInteger("row")));
//							stmt2.setString(13,siteMapName.get(priceObject.getInteger("row")));
							stmt2.setString(10,stringGetDepartureDate);
//							stmt2.setString(15,siteMapTime.get(priceObject.getInteger("col")));
							stmt2.setString(11,classesForSale.getId());
							stmt2.setInt(12,0);
							stmt2.addBatch();
							if(count2 % 3000 == 0){
								stmt2.executeBatch();
								connection.commit();
							}
							count2 ++;
						}
					}

				}
				stmt1.executeBatch();
				stmt2.executeBatch();
				connection.commit();
			}
		});
		session.close();
		return "1";
	}

	public String delete(ZtcClasses ztcClasses) {
		String sql = "delete from ztc_classes where 1=1";
		if (ztcClasses.getId() != null) {
			sql += " and id='" + ztcClasses.getId() + "'";
		}
		if (ztcClasses.getFkZtcRouteId() != null) {
			sql += " and fk_ztc_route_id='" + ztcClasses.getFkZtcRouteId() + "'";
		}
		if (ztcClasses.getSeatNumber() != null) {
			sql += " and seat_number='" + ztcClasses.getSeatNumber() + "'";
		}
		if (ztcClasses.getChildSeatNumber() != null) {
			sql += " and child_seat_number='" + ztcClasses.getChildSeatNumber() + "'";
		}
		if (ztcClasses.getFkAddUserId() != null) {
			sql += " and fk_add_user_id='" + ztcClasses.getFkAddUserId() + "'";
		}
		if (ztcClasses.getFkModifyUserId() != null) {
			sql += " and fk_modify_user_id='" + ztcClasses.getFkModifyUserId() + "'";
		}
		if (ztcClasses.getDepartureType() != null) {
			sql += " and departure_type='" + ztcClasses.getDepartureType() + "'";
		}
		if (ztcClasses.getDayFrequenceType() != null) {
			sql += " and day_frequency_type='" + ztcClasses.getDayFrequenceType() + "'";
		}
		if (this.currentSession().createNativeQuery(sql, ZtcClasses.class).executeUpdate() >= 0) {
			return "1";
		}
		return "0";
	}

	public List<ZtcClasses> searchInfoEquals(ZtcClasses ztcClasses) {
		String sql = "select * from ztc_classes  where 1=1";
		if (ztcClasses.getName() != null) {
			sql += " and name='" + ztcClasses.getName() + "'";
		}
		if (ztcClasses.getRemark() != null) {
			sql += " and remark='" + ztcClasses.getRemark() + "'";
		}
		if (ztcClasses.getTicketStatus() != null) {
			sql += " and ticket_status='" + ztcClasses.getTicketStatus() + "'";
		}
		if (ztcClasses.getCarType() != null) {
			sql += " and car_type='" + ztcClasses.getCarType() + "'";
		}
		if (ztcClasses.getIsRefund() != null) {
			sql += " and is_refund='" + ztcClasses.getIsRefund() + "'";
		}
		if (ztcClasses.getPreSaleDays() != null) {
			sql += " and pre_sale_days='" + ztcClasses.getPreSaleDays() + "'";
		}
		if (ztcClasses.getDepartureDate() != null) {
			sql += " and departure_time='" + DATE_FORMAT.format(ztcClasses.getDepartureDate()) + "'";
		}
		if (ztcClasses.getAddTime() != null) {
			sql += " and add_time='" + DATE_FORMAT.format(ztcClasses.getAddTime()) + "'";
		}
		if (ztcClasses.getModifyTime() != null) {
			sql += " and modify_time='" + DATE_FORMAT.format(ztcClasses.getModifyTime()) + "'";
		}
		if (ztcClasses.getFkZtcCarId() != null) {
			sql += " and fk_ztc_car_id='" + ztcClasses.getFkZtcCarId() + "'";
		}
		if (ztcClasses.getFkZtcDriverId() != null) {
			sql += " and fk_ztc_driver_id='" + ztcClasses.getFkZtcDriverId() + "'";
		}
		if (ztcClasses.getFkZtcRouteId() != null) {
			sql += " and fk_ztc_route_id='" + ztcClasses.getFkZtcRouteId() + "'";
		}
		if (ztcClasses.getFkTransportId() != null) {
			sql += " and fk_transport_id='" + ztcClasses.getFkTransportId() + "'";
		}
		if (ztcClasses.getFkAddUserId() != null) {
			sql += " and fk_add_user_id='" + ztcClasses.getFkAddUserId() + "'";
		}
		if (ztcClasses.getFkModifyUserId() != null) {
			sql += " and fk_modify_user_id='" + ztcClasses.getFkModifyUserId() + "'";
		}
		if (ztcClasses.getDepartureType() != null) {
			sql += " and departure_type='" + ztcClasses.getDepartureType() + "'";
		}
		if (ztcClasses.getDayFrequenceType() != null) {
			sql += " and day_frequency_type='" + ztcClasses.getDayFrequenceType() + "'";
		}
		if (ztcClasses.getWeekAllDays() != null) {
			sql += " and week_all_days='" + ztcClasses.getWeekAllDays() + "'";
		}
		if (ztcClasses.getMonthAllDays() != null) {
			sql += " and month_all_days='" + ztcClasses.getMonthAllDays() + "'";
		}
		if (ztcClasses.getId() != null) {
			sql += " and id='" + ztcClasses.getId() + "'";
		}
		sql += " order by departure_date";
		List<ZtcClasses> list = this.currentSession().createNativeQuery(sql, ZtcClasses.class).getResultList();
		return list;
	}

	public List<ZtcClasses> searchInfoLikes(ZtcClasses ztcClasses, String transportType, String pageNumber) {
		String sql = "select * from ztc_classes  where 1=1";
		if (ztcClasses.getName() != null) {
			sql += " and name like '%" + ztcClasses.getName() + "%'";
		}
		if (ztcClasses.getRemark() != null) {
			sql += " and remark like '%" + ztcClasses.getRemark() + "%'";
		}
		if (!"0".equals(transportType)) {
			if (ztcClasses.getFkTransportId() != null) {
				sql += " and fk_transport_id='" + ztcClasses.getFkTransportId() + "'";
			}
		}
		//sql += " order by ticket_status asc,add_time desc";
		sql += " order by ticket_status asc,departure_date desc";
		int startIndex = 0;
		int endIndex = 10;
		if (pageNumber == null) {
			pageNumber = "0";
		} else {
			int number = Integer.parseInt(pageNumber);
			if (!"0".equals(pageNumber)) {
				number = number - 1;
			}
			startIndex = (number) * pageSize;
		}
		sql += " limit " + startIndex + "," + endIndex;
		List<ZtcClasses> list = this.currentSession().createNativeQuery(sql, ZtcClasses.class).getResultList();
		return list;
	}

	public List<ZtcClasses> searchOverdueClasses() {
		//String sql = "select * from ztc_classes  where ztc_classes.ticket_status='0' and ztc_classes.departure_type='0' and CURDATE()>=DATE_ADD(ztc_classes.departure_date,INTERVAL 35 DAY) order by ztc_classes.departure_date";
		String sql = "select * from ztc_classes  where ztc_classes.ticket_status='0' and ztc_classes.departure_type='0' order by ztc_classes.departure_date";
		List<ZtcClasses> list = this.currentSession().createNativeQuery(sql, ZtcClasses.class).getResultList();
		return list;
	}

	public String update(ZtcClasses ztcClasses) {
		this.getHibernateTemplate().saveOrUpdate(ztcClasses);
		if (ztcClasses.getId() != null) {
			return "1";
		}
		return "0";
	}

	public int getCount(String name, String transportType, String transportId) {
		String sql = "select count(*) from ztc_classes where 1=1";
		if (name != null) {
			sql += " and name like '%" + name + "%'";
		}
		if (!"0".equals(transportType)) {
			if (transportId != null) {
				sql += " and fk_transport_id='" + transportId + "'";
			}
		}
		sql += " order by departure_date desc";
		@SuppressWarnings("unchecked")
		List<Object> li = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createNativeQuery(sql)
				.getResultList();
		return new Integer(String.valueOf(li.get(0)));
	}
}